﻿// Change all .Text to .Value as string (test case the column reduce too narrow to display text)
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Text.RegularExpressions;

namespace BHA
{
    public partial class PerfPlan
    {
        /*string[] REGEX = new string[] {"well", "zone", "top", "bot", "interval|leng|long", "temp", "pres", "eda|delay", "gun.*run", 
            "back.*up", "perf.*type"};
        string[] NAME = new string[] {"PERFPLAN_WELL", "PERFPLAN_ZONE", "PERFPLAN_*/
        Dictionary<string, Excel.Range> TITLE;
        const string WELL_REGEX = "well";
        const string WELL_NAME = "PERFPLAN_WELL";
        const string ZONE_REGEX = "zone";
        const string ZONE_NAME = "PERFPLAN_ZONE";
        const string TOP_REGEX = "top";
        const string TOP_NAME = "PERFPLAN_TOP";
        const string BOT_REGEX = "bot";
        const string BOT_NAME = "PERFPLAN_BOT";
        const string INTERVAL_REGEX = "interval|leng";
        const string INTERVAL_NAME = "PERFPLAN_INTERVAL";
        const string TEMP_REGEX = "temp";
        const string TEMP_NAME = "PERFPLAN_TEMP";
        const string PRESSURE_REGEX = "pres";
        const string PRESSURE_NAME = "PERFPLAN_PRESSURE";
        const string EDA_REGEX = "eda|delay";
        const string EDA_NAME = "PERFPLAN_EDA";
        const string GUNPERRUN_REGEX = "gun.*run";
        const string GUNPERFRUN_NAME = "PERFPLAN_GUNPERRUN";
        public const string BACKUP_REGEX = "back.*up";
        const string BACKUP_NAME = "PERFPLAN_BACKUP";
        const string PERFTYPE_REGEX = "perf.*type";
        const string PERFTYPE_NAME = "PERFPLAN_PERFTYPE";

        private void PerfPlan_Startup(object sender, System.EventArgs e)
        {
            TITLE = new Dictionary<string, Excel.Range>();
            foreach (Excel.Range title_cell in Application.Intersect(PERFPLAN_TITLE.RefersToRange, UsedRange))
            {
                if (string.IsNullOrWhiteSpace(title_cell.Text)) continue;
                if (Regex.IsMatch(title_cell.Text, WELL_REGEX, RegexOptions.IgnoreCase)) title_cell.Name = WELL_NAME;
                else if (Regex.IsMatch(title_cell.Text, ZONE_REGEX, RegexOptions.IgnoreCase)) title_cell.Name = ZONE_NAME;
                else TITLE.Add(Regex.Match(title_cell.Text, @"([a-zA-Z\s\.]+)\(?.*\)?").Groups[1].Value.Trim(), title_cell);
                //TITLE.Add(title_cell.Text, title_cell);
            }
        }

        private void PerfPlan_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.PERFTYPE_COL.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.PERFTYPE_COL_Change);
            this.Startup += new System.EventHandler(this.PerfPlan_Startup);
            this.Shutdown += new System.EventHandler(this.PerfPlan_Shutdown);

        }

        #endregion

        public void RefreshValidate()
        {
            StringBuilder sb = new StringBuilder();
            foreach (PerfType perftype in Globals.DBEquip.DatabasePerfType)
            {
                sb.Append(perftype.Description + ",");
            }
            PERFTYPE_COL.Validation.Add(Excel.XlDVType.xlValidateList,
                System.Type.Missing, Excel.XlFormatConditionOperator.xlBetween,
                sb.ToString());
        }

        private void butCompact_Click(object sender, EventArgs e)
        {
            Excel.Range firstSap = null;
            Excel.Range select = this.Application.Selection;
            List<ItemSAP> list = new List<ItemSAP>();
            foreach (Excel.Range row in select.Rows)
            {   // Duyet qua cac hang
                foreach (Excel.Range col in row.Columns)
                {   // Duyet qua cac cot
                    string sapno = ItemSAP.GetExtSAP(col.Text);
                    if (string.IsNullOrWhiteSpace(sapno)) continue;
                    // Neu la SAP Number
                    if (firstSap == null) firstSap = col; // Identify First SAP Row
                    bool found = false;
                    foreach (ItemSAP item in list)
                    {   // Duyet qua List neu trung NoSAP thi Add them Quantity
                        if (item.NoSAP == sapno) { item.TryAddQuantity(col.Offset[0, 1].Text); found = true; }
                    }
                    if (!found)
                    {
                        ItemSAP item = new ItemSAP();
                        item.NoSAP = sapno;
                        item.DescriptionUSE = col.Offset[0, -1].Text;
                        item.TrySetQuantity(col.Offset[0, 1].Text);
                        list.Add(item);
                    }
                }
            }

            // Now Fill Into Next Column the Compact List
            if (firstSap != null) FillExcel(firstSap.Offset[0, 4], list);
        }
        void FillExcel(Excel.Range TopLeftCell, List<ItemSAP> list)
        {
            for (int i = 0; i < list.Count; i++)
            {
                TopLeftCell.Offset[i, 0].Value = list[i].DescriptionUSE;
                TopLeftCell.Offset[i, 1].Value = list[i].NoSAP;
                TopLeftCell.Offset[i, 2].Value = list[i].Quantity;
                TopLeftCell.Offset[i, 2].NumberFormat = string.Format("General \"{0}\"", list[i].Unit);
            }

            TopLeftCell.Offset[0, 2].AutoFit();
            TopLeftCell.Offset[0, 1].AutoFit();
            TopLeftCell.Offset[0, 0].AutoFit();
        }
        private void butExtend_Click(object sender, EventArgs e)
        {
            Excel.Range firstSap = null;
            Excel.Range select = this.Application.Selection;
            List<ItemSAP> list = new List<ItemSAP>();
            foreach (Excel.Range row in select.Rows)
            {   // Duyet qua cac hang
                foreach (Excel.Range col in row.Columns)
                {   // Duyet qua cac cot
                    string sapno = ItemSAP.GetExtSAP(col.Text); // Lay Sapno
                    if (string.IsNullOrWhiteSpace(sapno)) continue; // Neu Khong phai Sapno cot ke
                    // Neu la SAP Number
                    if (firstSap == null) firstSap = col; // Identify First SAP Row

                    // Save SAP Item from DB To List
                    bool found = false;
                    foreach (ItemSAP item in Globals.DBEquip.DatabaseEquip)
                    {
                        if (item.NoSAP == sapno) { list.Add(item.Clone(col.Offset[0, 1].Text)); found = true; }
                    }
                    if (!found) // If not found in DB then save current to List
                    {
                        ItemSAP item = new ItemSAP();
                        item.NoSAP = sapno;
                        item.DescriptionUSE = col.Offset[0, -1].Text;
                        item.TrySetQuantity(col.Offset[0, 1].Text);
                        list.Add(item);
                    }
                }
            }

            // Now Fill Into Next Column the Compact List
            if (firstSap != null) FillExcelExt(firstSap.Offset[0, 4], list);
        }
        void FillExcelExt(Excel.Range TopLeftCell, List<ItemSAP> list)
        {
            List<ItemSAP> listext = new List<ItemSAP>();
            foreach (ItemSAP item in list)
            {
                if (item.Items.Count == 0)
                {
                    bool found = false;
                    foreach (ItemSAP eitem in listext)
                    {
                        if (eitem.NoSAP == item.NoSAP) { eitem.Quantity += item.Quantity; found = true; }
                    }
                    if (!found) listext.Add(item);
                }
                else
                {
                    foreach (ItemSAP subitem in item.Items)
                    {
                        bool found = false;
                        foreach (ItemSAP eitem in listext)
                        {
                            if (eitem.NoSAP == subitem.NoSAP) { eitem.Quantity += item.Quantity * subitem.Quantity; found = true; }
                        }
                        if (!found) listext.Add(subitem.Clone(item.Quantity * subitem.Quantity));
                    }
                }
            }

            for (int i = 0; i < listext.Count; i++)
            {
                FillCell(TopLeftCell.Offset[i, 0], listext[i]);
            }

            /*for (int i = 0, row = 0; i < list.Count; i++)
            {
                FillCell(TopLeftCell.Offset[row++, 0], list[i], true);
                foreach (ItemSAP item in list[i].Items)
                {
                    FillCell(TopLeftCell.Offset[row++, 0], item);
                }
            }*/

            TopLeftCell.Offset[0, 0].AutoFit();
            TopLeftCell.Offset[0, 1].AutoFit();
            TopLeftCell.Offset[0, 2].AutoFit();
        }
        void FillCell(Excel.Range TopLeftCell, ItemSAP item, bool bold = false)
        {
            TopLeftCell.Offset[0, 0].Value = item.DescriptionUSE;
            if (bold) TopLeftCell.Offset[0, 0].Font.Bold = true;
            TopLeftCell.Offset[0, 1].Value = item.NoSAP;
            if (bold) TopLeftCell.Offset[0, 1].Font.Bold = true;
            TopLeftCell.Offset[0, 2].Value = item.Quantity;
            if (bold) TopLeftCell.Offset[0, 2].Font.Bold = true;
            TopLeftCell.Offset[0, 2].NumberFormat = string.Format("General \"{0}\"", item.Unit);
        }
        private void butFill_Click(object sender, EventArgs e)
        {
            Excel.Range firstSap = null;
            Excel.Range select = this.Application.Selection;
            List<ItemSAP> list = new List<ItemSAP>();
            foreach (Excel.Range row in select.Rows)
            {   // Duyet qua cac hang
                foreach (Excel.Range col in row.Columns)
                {   // Duyet qua cac cot
                    string sapno = ItemSAP.GetExtSAP(col.Text);
                    if (string.IsNullOrWhiteSpace(sapno)) continue;
                    // Neu la SAP Number
                    if (firstSap == null) firstSap = col; // Identify First SAP Row
                    bool found = false;
                    foreach (ItemSAP item in list)
                    {   // Duyet qua List neu trung NoSAP thi Add them Quantity
                        if (item.NoSAP == sapno) { item.TryAddQuantity(col.Offset[0, 1].Text); found = true; }
                    }
                    if (!found)
                    {
                        ItemSAP item = new ItemSAP();
                        item.NoSAP = sapno;
                        item.DescriptionUSE = col.Offset[0, -1].Text;
                        item.TrySetQuantity(col.Offset[0, 1].Text);
                        list.Add(item);
                    }
                }
            }

            // Have List of Equipment
            foreach (Excel.Range r in Globals.TrackEquip.MT2X_Inv.RefersToRange)
            {
                string s = r.Offset[0, -8].Text;
                foreach (ItemSAP item in list)
                {
                    if (s == item.NoSAP) { r.Value = item.Quantity; r.NumberFormat = string.Format("General \"{0}\"", item.Unit); }
                }
            }
        }

        private void PERFTYPE_COL_Change(Excel.Range Target)
        {
            if (Target.Count > 100) return; // Insert or Delete Row
            foreach (Excel.Range cell_perftype in Target)
            {   // Each cell_perftype change
                foreach (PerfType perftype in Globals.DBEquip.DatabasePerfType)
                {   // Each PerfType in DB
                    if (cell_perftype.Text == perftype.Description)
                    {   // If value found in PerfType Database
                        StringBuilder varcode = new StringBuilder();
                        foreach (string title in TITLE.Keys)
                        {  // Create "{Interval}300,{EDA}3"
                            varcode.Append("{" + title + "}" + Cells[cell_perftype.Row, TITLE[title].Column].Text + ",");
                        }
                        PerfType ptype = perftype.Clone(varcode.ToString().TrimEnd(new char[] { ',' }));
                        ptype.WellName = FindWellName(cell_perftype);
                        ptype.ZoneName = Cells[cell_perftype.Row, Range[ZONE_NAME].Column].Text;
                        InsertEquipment(Cells[cell_perftype.Row, cell_perftype.Column + 1], ptype.ExtendList(Globals.DBEquip.DatabaseEquip));
                        FillEquipment(Cells[cell_perftype.Row + 1, cell_perftype.Column + 5], ptype.ExtendList());
                    }
                }
            }
        }

        bool IsZoneCell(Excel.Range zonecell)
        {
            if (string.IsNullOrWhiteSpace(zonecell.Text)) return false;
            if (zonecell.Address == Range[ZONE_NAME].Address) return false;
            if (zonecell.Column != Range[ZONE_NAME].Column) return false;

            // TO HERE MEAN : Same Column with ZONE_NAME Title & != Blank
            return true;
        }

        string FindWellName(Excel.Range CurrentCell)
        {
            // Giam Row tu vi tri Row hien tai len tren trong cot WellName
            for (int i = CurrentCell.Row; i > 0; i--)
            {
                string s = Cells[i, Range[WELL_NAME].Column].Text;
                if (string.IsNullOrWhiteSpace(s)) continue;
                return s;
            }
            return "Unknown";
        }

        private void FillEquipment(Excel.Range AboveLeftCell, List<ItemSAP> list)
        {
            Application.EnableEvents = false;
            EnableCalculation = false;
            Application.ScreenUpdating = false;
            try
            {
                int i = 0;
                foreach (ItemSAP item in list)
                {
                    AboveLeftCell.Offset[i, 0].Value2 = item.NoSAP;
                    AboveLeftCell.Offset[i, 0].NumberFormat = "@";
                    AboveLeftCell.Offset[i, 1].Value2 = item.DescriptionUSE;
                    AboveLeftCell.Offset[i, 1].NumberFormat = "@";
                    AboveLeftCell.Offset[i, 2].Value2 = item.Quantity;
                    AboveLeftCell.Offset[i, 2].Font.Bold = true;
                    AboveLeftCell.Offset[i, 2].NumberFormat = string.Format("General \"{0}\"", item.Unit);
                    i++;
                }
                AboveLeftCell.Offset[i, 0].EntireColumn.AutoFit();
                AboveLeftCell.Offset[i, 1].EntireColumn.AutoFit();
                AboveLeftCell.Offset[i, 2].EntireColumn.AutoFit();
            }
            finally
            {
                Application.EnableEvents = true;
                EnableCalculation = true;
                Application.ScreenUpdating = true;
            }
        }

        private void InsertEquipment(Excel.Range AboveLeftCell, List<ItemSAP> list)
        {
            Excel.Range BelowLeftCell = AboveLeftCell.Offset[1, 0];
            Application.EnableEvents = false;
            EnableCalculation = false;
            Application.ScreenUpdating = false;
            try
            {
                foreach (ItemSAP item in list)
                {
                    BelowLeftCell.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
                    BelowLeftCell.Offset[-1, 0].Value2 = item.NoSAP;
                    BelowLeftCell.Offset[-1, 0].NumberFormat = "@";
                    BelowLeftCell.Offset[-1, 1].Value2 = item.DescriptionUSE;
                    BelowLeftCell.Offset[-1, 1].NumberFormat = "@";
                    BelowLeftCell.Offset[-1, 2].Value2 = item.Quantity;
                    BelowLeftCell.Offset[-1, 2].Font.Bold = true;
                    BelowLeftCell.Offset[-1, 2].NumberFormat = string.Format("General \"{0}\"", item.Unit);
                }

                GroupRange(AboveLeftCell.Row + 1, BelowLeftCell.Row - 1);
                BelowLeftCell.Offset[0, 0].EntireColumn.AutoFit();
                BelowLeftCell.Offset[0, 1].EntireColumn.AutoFit();
                BelowLeftCell.Offset[0, 2].EntireColumn.AutoFit();
            }
            finally
            {
                Application.EnableEvents = true;
                EnableCalculation = true;
                Application.ScreenUpdating = true;
            }
        }

        private void GroupRange(int startRow, int endRow, string name = null)
        {
            Excel.Range range = this.Rows[string.Format("{0}:{1}", startRow, endRow)];
            if (name != null) range.Name = name;
            range.Group();
        }

        public List<ItemZone> RefreshAllItemSAP()
        {
            ItemZone zoneitem = null;
            //Dictionary<string, List<ItemZone>> zonelist = new Dictionary<string, List<ItemZone>>();
            List<ItemZone> zonelist = new List<ItemZone>();
            foreach (Excel.Range zonecell in Application.Intersect(Range[ZONE_NAME].EntireColumn, UsedRange))
            {   // Duyet qua Column ZONE_NAME
                if (IsZoneCell(zonecell))
                {   // Zone Cell Found, Save Old Item, Init New Item
                    if (zoneitem != null) zonelist.Add(zoneitem); // Save Old ZoneItem
                    zoneitem = new ItemZone(zonecell.Text, FindWellName(zonecell)); // Create new ZoneItem
                }
                else
                {   // Zone Cell Da Found, Add ItemSAP
                    if (zoneitem != null)
                    {   // Check ItemSAP at PERTYPE_COL + 1
                        Excel.Range sapcell = Cells[zonecell.Row, PERFTYPE_COL.Column + 1];
                        if (ItemSAP.IsExtSAP(sapcell.Text))
                            zoneitem.TryAdd(sapcell.Text, sapcell.Offset[0, 1].Text, sapcell.Offset[0, 2].Text);
                    }
                }
            }
            if (zoneitem != null) zonelist.Add(zoneitem);
            return zonelist;
        }
    }
}
